# _*_ coding:utf-8 _*_
# @File  : datalib.py
# @Time  : 2022-01-17  08:22
# @Author: zizle
import datetime
import asyncio
import itertools
import statistics
from itertools import groupby
from io import BytesIO
from typing import List

import openpyxl
import pandas as pd
from pydantic import BaseModel, ValidationError, validator, constr, conint, datetime_parse
from fastapi import APIRouter, Body, Query, Header, UploadFile, Form, BackgroundTasks
from fastapi.encoders import jsonable_encoder

from db import RuizyEDBConnection, FAConnection, RedisConnection
from hutool import security, utils as tool_utils
from v1_all_api.constant import DATABASE_INDEX_GROUPS
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse
from logger import logger

datalib_api = APIRouter()


class TableItem(BaseModel):
    source_plat: constr(min_length=1, max_length=16)
    source_id: constr(min_length=1, max_length=16)
    name_zh: constr(min_length=1, max_length=100)
    frequency: constr(min_length=1, max_length=1)
    unit: constr(min_length=0, max_length=32) = ''
    raw_source: constr(min_length=1, max_length=32)
    startdate: datetime_parse.date
    enddate: datetime_parse.date = None
    refresh: datetime_parse.date = None
    state: conint(ge=0, le=1)
    three_plat: constr(min_length=0, max_length=32) = ''
    note: constr(min_length=0, max_length=32) = ''
    is_open: conint(ge=0, le=1)
    variety: str = ''

    @validator('frequency')
    def valid_frequency(cls, value):
        if value not in ['日', '周', '月', '季', '年']:
            raise ValidationError('frequency错误')
        return value


@datalib_api.post('/')  # 建立一个新数据指标表
def create_table(table_item: TableItem = Body(...)):
    table_item.startdate = table_item.startdate.strftime('%Y-%m-%d')
    # 验证数据
    belong_variety = tool_utils.full_width_to_half_width(table_item.variety).upper().split(',')
    # 过滤不正确的品种数据
    fa_conn = FAConnection()
    sys_variety = fa_conn.query("SELECT id,variety_en FROM ruizy_variety;")
    sys_variety_list = [v['variety_en'] for v in sys_variety]
    belong_variety = list(filter(lambda x: x in sys_variety_list, belong_variety))
    # 构建数据，创建数据表记录
    edb_conn = RuizyEDBConnection()
    index_dict = jsonable_encoder(table_item)
    index_dict['variety'] = ','.join(belong_variety)
    index_dict['creator_id'] = 0
    if edb_conn.create_table(table_param=index_dict):
        return AllResponse.create_successfully()
    else:
        return AllResponse.server_error()


@datalib_api.delete('/')  # 删除一个数据指标
def delete_table(ruizy_token: str = Header(...), indexid: int = Body(..., ge=1, embed=True)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    if not security.operator_is_super_admin(user_obj):
        return AllResponse.validate_error(msg='数据就是财富，您不能进行这样的操作!')
    edb_conn = RuizyEDBConnection()
    # 查询数据id
    sql = "SELECT id,source_plat,source_id FROM all_ruizy_sheet WHERE id=%s LIMIT 1;"
    index_obj = edb_conn.query(sql, param=[indexid], fetchone=True, keep_conn=True)[0]
    if not index_obj:
        edb_conn.close()
        return AllResponse.validate_error(msg='数据指标不存在!')
    index_name = f'{index_obj["source_plat"]}_{index_obj["source_id"]}'
    # 检测是否有表引用这个指标。是否有图引用这个指标
    used_sheet_sql = "SELECT id FROM datalib_sheet_column WHERE INSTR(formula,%s)>0;"
    used_chart_sql = "SELECT id FROM datalib_variety_chart WHERE INSTR(indication,%s)>0;"
    fa_db = FAConnection()
    is_used_in_sheet = fa_db.query(used_sheet_sql, param=[index_name], keep_conn=True)
    if is_used_in_sheet:
        edb_conn.close()
        fa_db.close()
        return AllResponse.validate_error(msg='该指标有数据表引用，无法删除!')
    is_used_in_chart = fa_db.query(used_chart_sql, param=[index_name])
    if is_used_in_chart:
        edb_conn.close()
        return AllResponse.validate_error(msg='该指标有图形引用，无法删除!')
    # 没引用可删除
    del_sql = "DELETE FROM all_ruizy_sheet WHERE id=%s LIMIT 1;"
    drop_sql = "DROP table %s;" % index_name
    success = edb_conn.execute_tasks(sql_list=[del_sql, drop_sql], param_list=[[indexid], None])
    return AllResponse.operate_successfully(msg='删除指标成功!') if success else AllResponse.server_error()


class CustomTableItem(TableItem):
    source_id: str = None
    source_plat: str = 'rj'
    raw_source: str = ''


@datalib_api.post('/csm/')  # 建立一个自定义指标表
def create_custom_index(ruizy_token: str = Header(...), index_item: CustomTableItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()

    # 验证数据
    belong_variety = tool_utils.full_width_to_half_width(index_item.variety).upper().split(',')
    # 过滤不正确的品种数据
    fa_conn = FAConnection()
    sys_variety = fa_conn.query("SELECT id,variety_en FROM ruizy_variety;")
    sys_variety_list = [v['variety_en'] for v in sys_variety]
    belong_variety = list(filter(lambda x: x in sys_variety_list, belong_variety))

    db_conn = RuizyEDBConnection()
    # 查询改用户下的自定义指标id的最大值
    id_sql = "SELECT MAX(source_id) As max_id FROM all_ruizy_sheet WHERE creator_id=%s;"
    id_obj = db_conn.query(id_sql, param=[user_obj['uid']], fetchone=True, keep_conn=True)[0]
    if id_obj['max_id']:
        suffix = int(id_obj['max_id'][-4:])
    else:
        suffix = 0
    if suffix >= 9998:
        return AllResponse.validate_error(msg='您创建的指标数量已达到上限!')
    source_id = 'R%04d%04d' % (user_obj['uid'], suffix + 1)
    index_dict = {
        'source_plat': 'rj',
        'source_id': source_id,
        'name_zh': index_item.name_zh,
        'frequency': index_item.frequency,
        'unit': index_item.unit,
        'raw_source':  index_item.raw_source,
        'startdate': index_item.startdate.strftime('%Y-%m-%d'),
        'note': index_item.note,
        'state': index_item.state,
        'is_open': index_item.is_open,
        'creator_id': user_obj['uid'],
        'variety': ','.join(belong_variety),
        'three_plat': index_item.three_plat
    }
    if db_conn.create_table(table_param=index_dict):
        return AllResponse.operate_successfully()
    else:
        return AllResponse.server_error()


def sheet_format_handler(t):
    f = '%Y-%m-%d'
    if t['startdate']:
        t['startdate'] = t['startdate'].strftime(f)
    if t['enddate']:
        t['enddate'] = t['enddate'].strftime(f)
    if t['refresh']:
        t['refresh'] = t['refresh'].strftime('%Y-%m-%d %H:%M')

    t['state_text'] = '正常'
    if t['state'] == 0:
        t['state_text'] = '停止'
        t['name_zh'] = '(停止)' + t['name_zh']
    t['create_time'] = t['create_time'].strftime('%Y-%m-%d %H:%M')
    return t


@datalib_api.get('/csm/')  # 查询自定义的指标列表
def retrieve_custom_index(ruizy_token: str = Header(...), kw: str = Query(None),
                          page: int = Query(..., ge=1), page_size: int = Query(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    start_index = (page - 1) * page_size
    keyword = kw if kw else 'kw'
    sql = """
        SELECT id,source_plat,source_id,name_zh,frequency,unit,raw_source,
            startdate,enddate,create_time,refresh,state,note,is_open,variety 
        FROM all_ruizy_sheet 
        WHERE creator_id=%s AND IF('kw'=%s,TRUE,name_zh LIKE %s OR source_id=%s) 
        ORDER BY create_time DESC LIMIT %s,%s;
    """
    prm = (user_obj['uid'], keyword, f'%{keyword}%', keyword, start_index, page_size)
    db_conn = RuizyEDBConnection()
    ret = db_conn.query_paginator(sql, param=prm, page=page, page_size=page_size)
    ret['data'] = list(map(sheet_format_handler, ret['data']))
    return AllResponse.operate_successfully(data=ret)


@datalib_api.get('/')  # 查询数据表，含关键字、品种检索
def query_table(kw: str = Query(None), page: int = Query(1), page_size: int = Query(30),
                frequency: str = Query(None), plat: str = Query(None), v: str = Query(None)):

    def filter_exclude_variety(t_variety, item):  # 过滤掉不在查询内的品种，但包含未分类到品种内的指标
        item_variety_list = item['variety'].split(',')
        return t_variety in item_variety_list or '' in item_variety_list

    if (frequency and frequency not in ['年', '季', '月', '周', '日']) or (plat and plat not in ['wind', 'ths']):
        return AllResponse.validate_error(msg='参数frequency、plat错误!')
    edb_conn = RuizyEDBConnection()
    if v:
        # 此处隐藏一个bug，当数据指标量超过99999时，得修正
        ret = edb_conn.query_table(kw=kw, plat=plat, frequency=frequency, page=1, page_size=99999)
        data_list = list(filter(lambda x: filter_exclude_variety(v, x), ret['records']))
        # 重新处理页码数据
        total_count = len(data_list)
        start_index = (page - 1) * page_size
        records = list(map(sheet_format_handler, data_list[start_index: start_index + page_size]))
    else:
        ret = edb_conn.query_table(kw=kw, plat=plat, frequency=frequency, page=page, page_size=page_size)
        records = list(map(sheet_format_handler, ret['records']))
        total_count = ret['total_count']
    response_data = {
        'records': records,
        'current_page': page,
        'total_count': total_count
    }
    return AllResponse.operate_successfully(data=response_data)


@datalib_api.get('/browser/')  # 查询类别下的数据指标
def query_group_table(kw: str = Query(None), t: str = Query(...), page: int = Query(1), page_size: int = Query(30)):
    if t not in DATABASE_INDEX_GROUPS.keys():
        return AllResponse.validate_error(msg='数据类别错误.')


    return AllResponse.operate_successfully()


class SheetRowItem(BaseModel):
    datadate: datetime_parse.date
    datavalue: float


class UpdateItem(BaseModel):
    dataid: conint(ge=1)
    row_value: List[SheetRowItem]


@datalib_api.post('/update/')  # 更新一个数据表
def update_table_sheet(update_item: UpdateItem = Body(...)):
    # print(sheet_id)
    # print(update_item.dataid)
    # print(update_item.row_value)
    db_conn = RuizyEDBConnection()
    if len(update_item.row_value) < 1:  # 没有数据,直接更新日期
        sql = 'UPDATE all_ruizy_sheet SET refresh=%s WHERE id=%s LIMIT 1;'
        db_conn.execute(sql, param=(datetime.datetime.today(), update_item.dataid))
        return AllResponse.operate_successfully()  # 不管成功失败，都返回成功

    # 查询数据的最新日期，并截取所需要的数据进行存储
    query_latest = 'SELECT id,source_id,source_plat,startdate,enddate FROM all_ruizy_sheet WHERE id=%s LIMIT 1;'
    sheet_obj = db_conn.query(query_latest, param=[update_item.dataid], fetchone=True, keep_conn=True)[0]
    if not sheet_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据表不存在，无法更新!')

    data_enddate = sheet_obj['enddate']
    # 处理数据
    df = pd.DataFrame(jsonable_encoder(update_item.row_value))
    if data_enddate:  # 表为空 没有数据
        data_enddate = data_enddate.strftime('%Y-%m-%d')
        df = df[df['datadate'] > data_enddate]
    if df.empty:
        return AllResponse.operate_successfully(msg='更新成功,没有新数据!')
    # 保存数据
    success = db_conn.save_table(table_param=sheet_obj, table_values=df.to_dict(orient='records'))
    return AllResponse.operate_successfully(msg='更新数据表成功!') if success else AllResponse.server_error()


class IndexDetailValueItem(BaseModel):
    dataid: conint(ge=1)
    value_id: conint(ge=1)
    datadate: datetime_parse.date
    datavalue: float


@datalib_api.put('/updateValue/')  # 更新一个指标内的某条数据
def update_index_value(ruizy_token: str = Header(...), value_item: IndexDetailValueItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 找到数据表，修改数据
    db_conn = RuizyEDBConnection()
    index_sql = 'SELECT id,source_id,source_plat,startdate,enddate,creator_id FROM all_ruizy_sheet WHERE id=%s LIMIT 1;'
    index_obj = db_conn.query(index_sql, param=[value_item.dataid], fetchone=True, keep_conn=True)[0]
    if not index_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据表不存在，更新失败!')
    if index_obj['creator_id'] == 0 and not security.operator_is_super_admin(user_obj):
        db_conn.close()
        return AllResponse.validate_error(msg='暂无权限更新本指标数据!')
    # 更新表，并更新数据的更新时间
    table_name = f'{index_obj["source_plat"]}_{index_obj["source_id"]}'
    update_sql = f'UPDATE {table_name} SET datavalue=%s WHERE id=%s LIMIT 1;'
    db_conn.execute(update_sql, param=[value_item.datavalue, value_item.value_id], keep_conn=True)

    refresh_date = datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S')
    db_conn.execute("UPDATE all_ruizy_sheet SET refresh=%s WHERE id=%s LIMIT 1;",
                    param=[refresh_date, value_item.dataid])
    return AllResponse.operate_successfully(msg='更新数据成功!')


@datalib_api.delete('/deleteValue/')  # 删除一个指标内的某条数据
def delete_index_value(ruizy_token: str = Header(...), value_item: IndexDetailValueItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    # 找到数据表，修改数据
    db_conn = RuizyEDBConnection()
    index_sql = 'SELECT id,source_id,source_plat,startdate,enddate,creator_id FROM all_ruizy_sheet WHERE id=%s LIMIT 1;'
    index_obj = db_conn.query(index_sql, param=[value_item.dataid], fetchone=True, keep_conn=True)[0]
    if not index_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据表不存在，删除数据值失败!')
    if index_obj['creator_id'] == 0 and not security.operator_is_super_admin(user_obj):
        db_conn.close()
        return AllResponse.validate_error(msg='这个数据不支持删除!')
    # 删除数据
    table_name = f'{index_obj["source_plat"]}_{index_obj["source_id"]}'
    del_sql = f'DELETE FROM {table_name} WHERE id=%s LIMIT 1;'
    success = db_conn.execute(del_sql, param=[value_item.value_id])
    return AllResponse.operate_successfully(msg='删除数据成功!') if success else AllResponse.server_error()


@datalib_api.post('/csm/update/')  # 更新一个自定义的数据指标的数据
def update_custom_index(ruizy_token: str = Header(...), update_item: UpdateItem = Body(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    db_conn = RuizyEDBConnection()

    if len(update_item.row_value) < 1:  # 没有数据,直接更新更新日期
        return AllResponse.validate_error(msg='没有填写正确的数据')  # 不管成功失败，都返回成功

    # 查询数据的最新日期，并截取所需要的数据进行存储
    query_latest = 'SELECT id,source_id,source_plat,startdate,enddate FROM all_ruizy_sheet WHERE id=%s LIMIT 1;'
    sheet_obj = db_conn.query(query_latest, param=[update_item.dataid], fetchone=True, keep_conn=True)[0]
    if not sheet_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据表不存在，无法更新!')

    data_enddate = sheet_obj['enddate']
    # 处理数据
    df = pd.DataFrame(jsonable_encoder(update_item.row_value))
    if data_enddate:  # 表为空 没有数据
        data_enddate = data_enddate.strftime('%Y-%m-%d')
        df = df[df['datadate'] > data_enddate]
    if df.empty:
        return AllResponse.operate_successfully(msg='更新成功,没有新数据!')
    # 保存数据
    success = db_conn.save_table(table_param=sheet_obj, table_values=df.to_dict(orient='records'))
    response_data = {
        'iid': update_item.dataid,
        'enddate': df['datadate'].max(),
        'refresh': datetime.datetime.today().strftime('%Y-%m-%d %H:%M')
    }
    return AllResponse.operate_successfully(data=response_data) if success else AllResponse.server_error()


@datalib_api.post('/csm/fileUpdate/')  # 文件添加一个数据指标的数据
async def first_update_custom_index(ruizy_token: str = Header(...), file: UploadFile = Form(...), iid: int = Form(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    content = await file.read()
    try:
        df = pd.read_excel(BytesIO(content))
        if df.columns.tolist() != ['日期', '数值']:
            raise ValueError('列格式错误!')
        # 格式化日期
        df['datadate'] = pd.to_datetime(df['日期'])
        df['datavalue'] = pd.to_numeric(df['数值'])
        del df['日期']
        del df['数值']
        await file.close()
    except Exception as e:
        logger.error('解析用户上传的文件失败:{}'.format(e))
        msg = '数据文件格式有误!列需为[日期、数值],数值列不能为空！'
        return AllResponse.validate_error(msg=msg)
    # 删除重复和na的行
    df.drop_duplicates(keep='last', inplace=True)
    df.dropna(axis=0, inplace=True)
    if df.empty:
        return AllResponse.operate_successfully(msg='更新成功，没有发现数据!')
    df['datadate'] = df['datadate'].apply(lambda x: x.strftime('%Y-%m-%d'))
    df['datavalue'] = df['datavalue'].astype(float)
    df.sort_values(by='datadate', inplace=True)
    # 解析上传的文件结束
    max_date = df['datadate'].max()
    # 查询iid的指标表，更新数据
    db_conn = RuizyEDBConnection()

    # 查询数据的最新日期，并截取所需要的数据进行存储
    query_latest = 'SELECT id,source_id,source_plat,startdate,enddate FROM all_ruizy_sheet WHERE id=%s LIMIT 1;'
    sheet_obj = db_conn.query(query_latest, param=[iid], fetchone=True, keep_conn=True)[0]
    if not sheet_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据表不存在，无法更新!')
    data_enddate = sheet_obj['enddate']
    # 处理数据
    if data_enddate:  # 如果有结束日期
        data_enddate = data_enddate.strftime('%Y-%m-%d')
        df = df[df['datadate'] > data_enddate]
    if df.empty:
        return AllResponse.operate_successfully(msg='更新成功,没有新数据!')
    # 保存数据
    success = db_conn.save_table(table_param=sheet_obj, table_values=df.to_dict(orient='records'))
    response_data = {
        'iid': iid,
        'enddate': max_date,
        'refresh': datetime.datetime.today().strftime('%Y-%m-%d %H:%M')
    }
    return AllResponse.create_successfully(data=response_data) if success else AllResponse.server_error()


@datalib_api.put('/csm/transferal/')  # 转移自己的指标数据给别人
def transfer_customer_index(ruizy_token: str = Header(...), touser: int = Body(..., embed=True)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    with RuizyEDBConnection() as cursor:
        c = cursor.execute("UPDATE all_ruizy_sheet SET creator_id=%s WHERE creator_id=%s;", [touser, person['uid']])
    return AllResponse.operate_successfully(msg=f'转移{c}个数据成功,请将更新文件进行交接...')


def formate_date_obj(dateobj, sheet_name, index_id):
    try:
        return dateobj.strftime('%Y-%m-%d')
    except Exception as e:
        logger.error(f'解析{sheet_name}的ID={index_id}错误: {e}')
        return pd.NaT


# 后台任务文件更新自定义指标
async def file_updating_background_task(user_obj, dataframe_list, name_list):
    # 查询该用户的所有自定义指标（不是其自定义的，就无法被更新）
    query_sql = """
        SELECT id,source_plat,source_id,name_zh,startdate,enddate FROM all_ruizy_sheet WHERE creator_id=%s;
    """
    rdb_conn = RuizyEDBConnection()
    custom_indexes = rdb_conn.query(query_sql, param=[user_obj['uid']], keep_conn=True)
    custom_indexes_dict = {ci['source_id']: ci for ci in custom_indexes}  # 转为dict
    for i, df in enumerate(dataframe_list):  # 遍历每个sheet的数据框
        columns = list(filter(lambda x: x != 'ID', df.columns.tolist()))
        for index_id in columns:
            c_index_obj = custom_indexes_dict.get(index_id)
            if not c_index_obj:
                continue
            await asyncio.sleep(0.6)
            # print(c_index_obj)
            index_df = df[['ID', index_id]]
            index_df = index_df.dropna()  # 去除没数据的行，包含日期列，此处日期列为ID
            index_df['ID'] = index_df['ID'].apply(lambda x: formate_date_obj(x, name_list[i], index_id))
            index_df = index_df.dropna()  # 再一次去除，ID解析后可能返回NAN
            # print(index_df)
            # continue
            # 截取数据
            if c_index_obj['enddate']:
                index_df = index_df[index_df['ID'] > c_index_obj['enddate'].strftime('%Y-%m-%d')]
            else:
                index_df = index_df[index_df['ID'] >= c_index_obj['startdate'].strftime('%Y-%m-%d')]
            if index_df.empty:
                # 只更新日期
                sql = 'UPDATE all_ruizy_sheet SET refresh=%s WHERE id=%s LIMIT 1;'
                rdb_conn.execute(sql, param=(datetime.datetime.today(), c_index_obj['id']), keep_conn=True)
                continue
            # 更新数据
            table_values = index_df.rename(columns={'ID': 'datadate', index_id: 'datavalue'}).to_dict(orient='records')
            # for row in table_values:
            #     print(row)
            rdb_conn.save_table(table_param=c_index_obj, table_values=table_values, keep_conn=True)
    rdb_conn.close()
    # 更新完毕后，设置redis更新状态
    status_key = 'fileUpdating_%d' % user_obj['uid']
    redis_conn = RedisConnection()
    redis_conn.set_value(status_key, 0, expires=1800)  # 设置时效为1小时的更新状态


def read_file_sheet(file_content, filename):
    dataframe_list = []
    dataframe_name_list = []
    current_name = ''
    try:
        excel_file = openpyxl.load_workbook(BytesIO(file_content), read_only=True)
        sheet_names = list(filter(lambda s_name: not s_name.lower().startswith('sheet'), excel_file.sheetnames))
        for name in sheet_names:
            current_name = name
            dataframe_list.append(pd.read_excel(BytesIO(file_content), sheet_name=name, skiprows=[0, 1]))
            dataframe_name_list.append(name)
    except Exception as e:
        logger.error('解析用户上传的文件:{}下的表:{}表发生错误:{}'.format(filename, current_name, e))
        return False, dataframe_list, dataframe_name_list
    else:
        return True, dataframe_list, dataframe_name_list


@datalib_api.post('/csm/fileManyUpdate/')  # 文件批量更新数据自定义指标数据
async def update_many_custom_index(ruizy_token: str = Header(...), file: UploadFile = Form(...)):
    user_obj = security.decrypt_access_token(ruizy_token)
    if not user_obj:
        return AllResponse.no_authorization()
    content = await file.read()
    # 使用loop读取文件，防止文件读取过久久时，影响其他访问。
    loop = asyncio.get_event_loop()
    success, dataframe_list, name_list = await loop.run_in_executor(None, read_file_sheet, content, file.filename)

    # try:
    #     excel_file = openpyxl.load_workbook(BytesIO(content), read_only=True)
    #     sheet_names = list(filter(lambda s_name: not s_name.lower().startswith('sheet'), excel_file.sheetnames))
    #     for name in sheet_names:
    #         print(name)
    #         dataframe_list.append(pd.read_excel(BytesIO(content), sheet_name=name, skiprows=[0, 1]))
    # except Exception as e:
    #     logger.error('解析用户上传的文件错误:{}'.format(e))

    if not success:
        return AllResponse.validate_error(msg='解析文件失败，请检查文件格式或数据格式！')
    # redis设置更新状态，启动后台任务，返回响应
    status_key = 'fileUpdating_%d' % user_obj['uid']
    redis_conn = RedisConnection()
    redis_conn.set_value(status_key, 1, expires=1800)  # 设置时效为0.5小时的更新状态
    # 这里虽然使用await只是为了不造成其他接口阻塞，该访问依然得等任务执行完，控制使用redis状态管理
    await file_updating_background_task(user_obj=user_obj, dataframe_list=dataframe_list, name_list=name_list)
    return AllResponse.operate_successfully(msg='上传成功，更新完成！')


@datalib_api.get('/csm/fileManyUpdateState/')  # 查询文件更新自定义指标的状态结果
def update_many_custom_index_state(ruizy_token: str = Header(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    redis_conn = RedisConnection()
    status_key = 'fileUpdating_%d' % person['uid']
    state = redis_conn.get_value(status_key)
    if not state:
        state = 0
    return AllResponse.operate_successfully(data={'state': state})


@datalib_api.get('/info/')  # 查询一个指标信息
def retrieve_table_sheet(iid: int = Query(..., ge=1),
                         page: int = Query(..., ge=0), page_size: int = Query(10, ge=10)):
    db_conn = RuizyEDBConnection()
    # 查询数据表信息
    info_sql = """
        SELECT id,source_id,source_plat,name_zh,frequency,unit,raw_source,startdate,enddate,refresh,
            state,note,is_open  
        FROM all_ruizy_sheet WHERE id=%s LIMIT 1;
    """
    table_info = db_conn.query(info_sql, param=[iid], fetchone=True, keep_conn=True)[0]
    if not table_info:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据不存在!')
    if table_info['refresh']:
        table_info['refresh'] = table_info['refresh'].strftime('%Y-%m-%d %H:%M')
    # 查询数据内容
    table_name = f'{table_info["source_plat"]}_{table_info["source_id"]}'
    if page > 0:
        start_index = (page - 1) * page_size
        query_sql = "SELECT id,datadate,datavalue FROM %s ORDER BY datadate DESC LIMIT %s,%s;" % (table_name,
                                                                                             start_index, page_size)
        ret = db_conn.query_paginator(query_sql, param=None, page=page, page_size=page_size)
    else:
        query_sql = "SELECT id,datadate,datavalue FROM %s;" % table_name
        ret = {'data': db_conn.query(query_sql)}
    response_data = {
        'index_info': table_info,
        **ret
    }
    return AllResponse.operate_successfully(data=response_data)


@datalib_api.get('/sequence/')  # 按日期获取指标数据
def date_sequence(iid: int = Query(...), start: str = Query(...), end: str = Query(...)):
    start_date = datetime_utils.auth_datetime_string(start, f='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期格式错误')
    with RuizyEDBConnection() as cursor:
        cursor.execute("""
            SELECT id,source_id,source_plat,name_zh,frequency,unit,raw_source,startdate,enddate,refresh,
                state,note,is_open  
            FROM all_ruizy_sheet WHERE id=%s LIMIT 1;
        """, [iid])
        index_info = cursor.fetchone()
        if not index_info:
            return AllResponse.server_no_content(msg='数据不存在')
        # 查询数据
        table_name = f'{index_info["source_plat"]}_{index_info["source_id"]}'
        query_sql = """
            SELECT id,datadate,datavalue FROM %s WHERE datadate>='%s' AND datadate<='%s' ORDER BY datadate;
        """ % (table_name, start_date, end_date)
        cursor.execute(query_sql)
        ret = {
            'index_info': index_info,
            'data': list(cursor.fetchall()),
            'start_date': start_date,
            'end_date': end_date
        }
    return AllResponse.operate_successfully(data=ret)


class BrushItem(BaseModel):
    index_id: conint(ge=1)
    start_date: str


@datalib_api.put('/brush/')  # 清空一个指标数据
def brush_table_data(ruizy_token: str = Header(...), brush: BrushItem = Body(...)):
    # 验证身份，只能操作自己的数据
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 验证数据
    new_start_date = datetime_utils.auth_datetime_string(brush.start_date, f='%Y-%m-%d')
    # 查询数据表信息
    db_conn = RuizyEDBConnection()
    info_sql = """
        SELECT id,source_id,creator_id,source_plat,name_zh,frequency,unit,raw_source,startdate,enddate,refresh,
            state,note,is_open  
        FROM all_ruizy_sheet WHERE id=%s LIMIT 1;
    """
    table_info = db_conn.query(info_sql, param=[brush.index_id], fetchone=True, keep_conn=True)[0]
    if not table_info:
        db_conn.close()
        return AllResponse.server_no_content(msg='数据不存在!')
    if not security.operator_is_super_admin(person) and table_info['creator_id'] != person['uid']:
        return AllResponse.operate_successfully(msg='请不要操作他人上传的数据信息!')
    current_time = datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S')
    if not new_start_date:
        new_start_date = table_info['startdate']
    table_name = f'{table_info["source_plat"]}_{table_info["source_id"]}'
    task_sql = [
        "UPDATE all_ruizy_sheet SET enddate=NULL,startdate=%s,refresh=%s WHERE id=%s LIMIT 1;",
        "TRUNCATE TABLE %s;" % table_name
    ]
    task_prm = [
        [new_start_date, current_time, brush.index_id],
        None
    ]
    success = db_conn.execute_tasks(task_sql, task_prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@datalib_api.get('/exists/')  # 查询一个平台指标是否存在
def index_exists(plat: str = Query(...), sid: str = Query(...)):
    sql = "SELECT id FROM all_ruizy_sheet WHERE source_plat=%s AND source_id=%s LIMIT 1;"
    db_conn = RuizyEDBConnection()
    ret = db_conn.query(sql, param=[plat, sid], fetchone=True)[0]
    return AllResponse.operate_successfully(data=ret)


@datalib_api.get('/indexGroups/')  # 查询各种数据指标分组及占有率
def get_index_group():
    ruizy_conn = RuizyEDBConnection()
    sql = "SELECT id,source_plat,source_id,name_zh,frequency,raw_source,startdate,enddate FROM all_ruizy_sheet " \
          "ORDER BY source_plat;"
    indexes = ruizy_conn.query(sql)
    response_data = []
    total_count = len(indexes)
    group_name = {'rj1': '系统', 'rj2': '用户', 'ths': '同花顺', 'wind': '万得'}
    for g, member in groupby(indexes, key=lambda x: x['source_plat']):
        group_member = list(member)
        if g == 'rj':
            rj1 = []
            rj2 = []
            for i in group_member:
                rj1.append(i) if i['source_id'].startswith('R0002') else rj2.append(i)
            response_data.append({
                'group': 'rj1',
                'name': group_name.get('rj1'),
                'count': len(rj1),
                'rate': round(len(rj1) / total_count, 4),
                'data': rj1
            })
            response_data.append({
                'group': 'rj2',
                'name': group_name.get('rj2'),
                'count': len(rj2),
                'rate': round(len(rj2) / total_count, 4),
                'data': rj2
            })
        else:
            response_data.append({
                'group': g,
                'name': group_name.get(g),
                'count': len(group_member),
                'rate': round(len(group_member) / total_count, 4),
                'data': group_member
            })
    return AllResponse.operate_successfully(data=response_data)

# --------------------------------------------------------------------------


@datalib_api.get('/indexData/')  # 根据指标名称查询对应细分数据
def get_index_data(detail_id: int = ...):
    with RuizyEDBConnection() as cursor:
        # 查询数据表信息
        info_sql = "SELECT * FROM all_ruizy_sheet WHERE id={};".format(detail_id)
        cursor.execute(info_sql)
        data = cursor.fetchall()
        if data[0]['unit']:
            ret1 = {
                "name": data[0]['name_zh'] + "(" + data[0]['frequency'] + ")" + "(" + data[0]['unit'] + ")",
                "raw_source": data[0]['raw_source'],
                "enddate": str(data[0]['enddate']),
                "unit": data[0]['unit']
            }
        else:
            ret1 = {
                "name": data[0]['name_zh'] + "(" + data[0]['frequency'],
                "raw_source": data[0]['raw_source'],
                "enddate": str(data[0]['enddate']),
                "unit": data[0]['unit']
            }
        if not data[0]['raw_source']:
            ret1["raw_source"] = "暂无"
        if not data[0]['refresh']:
            ret1["enddate"] = "暂无"
        secondary_index = data[0]['source_plat'] + "_" + data[0]['source_id']
        info_sql = "SELECT datadate,datavalue FROM {};".format(secondary_index)
        cursor.execute(info_sql)
        data = cursor.fetchall()
        if data:
            latest_date = round(data[len(data)-1]['datavalue'], 2)
            increase_date = round(data[len(data)-1]['datavalue'] - data[len(data)-2]['datavalue'], 2)
            if data[len(data)-2]['datavalue'] != 0:
                increase_rate = "{:.2%}".format(round(increase_date / data[len(data)-2]['datavalue'], 2))
            else:
                increase_rate = "0.00%"
            sample_num = len(data)
            min_date = round(min(e['datavalue'] for e in data), 2)
            max_date = round(max(e['datavalue'] for e in data), 2)
            average_date = round(sum(e['datavalue'] for e in data) / sample_num, 2)
            date_list = []
            for e in data:
                date_list.append(e['datavalue'])
            date_list.sort()
            middle_date = round(get_mid_date(date_list), 2)
            standard_date = round(statistics.stdev(date_list), 2)
            ret2 = {
                "latest_date": latest_date,
                "increase_date": increase_date,
                "increase_rate": increase_rate,
                "sample_num": sample_num,
                "min_date": min_date,
                "max_date": max_date,
                "average_date": average_date,
                "middle_date": middle_date,
                "standard_date": standard_date
            }
            ret3 = {"datadate": [], "datavalue": []}
            for e in data:
                ret3["datadate"].append(e['datadate'])
                ret3["datavalue"].append(e['datavalue'])
            ret = {**ret1, **ret2, **ret3}
        else:
            latest_date = "暂无数据",
            increase_date = '暂无数据',
            increase_rate = '暂无数据',
            sample_num = '暂无数据',
            min_date = '暂无数据',
            max_date = '暂无数据',
            average_date = '暂无数据',
            middle_date = '暂无数据',
            standard_date = '暂无数据'
            ret2 = {
                "latest_date": latest_date[0],
                "increase_date": increase_date[0],
                "increase_rate": increase_rate[0],
                "sample_num": sample_num[0],
                "min_date": min_date[0],
                "max_date": max_date[0],
                "average_date": average_date[0],
                "middle_date": middle_date[0],
                "standard_date": standard_date
            }
            ret = {**ret1, **ret2}
    return AllResponse.operate_successfully(data=ret)


def get_mid_date(x):
    x.sort()
    half = len(x) // 2
    return (x[half] + x[~half])/2


@datalib_api.delete('/deleteManyWithDate/')  # 根据日期批量删除数据
def delete_many_with_date(indexId: int = Query(...)):
    return AllResponse.operate_successfully()
    ruizy_db = RuizyEDBConnection()
    exist_obj = ruizy_db.query('SELECT id,source_plat,source_id FROM all_ruizy_sheet WHERE id=%s LIMIT 1;',
                               param=[indexId], fetchone=True, keep_conn=True)[0]
    # print(exist_obj)

    if not exist_obj:
        ruizy_db.close()
        return AllResponse.operate_successfully(msg='数据表不存在！')

    table_name = f'{exist_obj["source_plat"]}_{exist_obj["source_id"]}'
    # print(table_name)
    sqls = [
        'DELETE FROM %s WHERE datadate>="2022-08-17";' % table_name,
        'UPDATE all_ruizy_sheet SET enddate="2022-08-16" WHERE id=%s LIMIT 1;' % indexId
    ]
    s = ruizy_db.execute_tasks(sql_list=sqls, param_list=[None, None])
    return AllResponse.operate_successfully() if s else AllResponse.server_error(msg='操作失败！')

